package com.google.code.ptrends.services.implementations.dataservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import org.apache.log4j.Logger;
import com.google.code.ptrends.common.SupplierIdentifiers;
import com.google.code.ptrends.common.entities.Manufacturer;
import com.google.code.ptrends.common.entities.Property;
import com.google.code.ptrends.common.entities.Session;
import com.google.code.ptrends.common.entities.SessionEvent;
import com.google.code.ptrends.entities.ETLItem;
import com.google.code.ptrends.entities.ItemRecord;
import com.google.code.ptrends.entities.ItemValueCollection;
import com.google.code.ptrends.entities.ItemValueRecord;
import com.google.code.ptrends.entities.PriceRecord;
import com.google.code.ptrends.services.DataService;

public class DataServiceImpl extends DataServiceBaseImpl implements DataService {

    private static final Logger LOG = Logger.getLogger(DataService.class);
    private final FinderServiceImpl finderService;
    private final ValuesServiceImpl valuesService;
    private final PriceServiceImpl priceService;
    private final SaverServiceImpl saverService;
    private final SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    public DataServiceImpl(final Connection connection) {
	super(connection);

	finderService = new FinderServiceImpl(connection);
	valuesService = new ValuesServiceImpl(connection);
	priceService = new PriceServiceImpl(connection);
	saverService = new SaverServiceImpl(connection);
    }

    @Override
    public ItemRecord findItem(final String model, int categoryID) throws SQLException {
	return finderService.findItem(model, categoryID);
    }

    @Override
    public ItemRecord findItem(final String model) throws SQLException {
	return finderService.findItem(model);
    }

    @Override
    public int saveItem(final ItemRecord item) throws SQLException {
	return saverService.saveItem(item);
    }

    @Override
    public PriceRecord findLastPrice(final int itemID, final int SupplierID) throws SQLException {
	return priceService.findLastPrice(itemID, SupplierID);
    }

    @Override
    public boolean findPrice(int itemID, int SupplierID, Date priceDate) throws SQLException {
	return priceService.findPrice(itemID, SupplierID, priceDate) != null;
    }

    @Override
    public boolean saveNewPrice(final PriceRecord price) throws SQLException {
	return priceService.savePrice(price);
    }

    public boolean saveNewPrice(final int itemID, final int supplierID, final float price,
	    final Date priceDate) throws SQLException {
	PriceRecord priceRecord = new PriceRecord();
	priceRecord.setItemID(itemID);
	priceRecord.setSupplierID(supplierID);
	priceRecord.setPrice(price);
	priceRecord.setPriceDate(priceDate);
	return priceService.savePrice(priceRecord);
    }

    @Override
    public float updatePrice(final PriceRecord price, final float newValue) throws SQLException {
	return priceService.updatePrice(price, newValue);
    }

    public float updatePrice(final int itemID, final int supplierID, final Date priceDate,
	    final float newPriceValue) throws SQLException {
	PriceRecord priceRecord = new PriceRecord();
	priceRecord.setItemID(itemID);
	priceRecord.setSupplierID(supplierID);
	priceRecord.setPriceDate(priceDate);
	return priceService.updatePrice(priceRecord, newPriceValue);
    }

    @Override
    public ItemValueCollection getValues(final int itemID) throws SQLException {
	return valuesService.getValues(itemID);
    }

    @Override
    public boolean saveValue(final int itemID, final ItemValueRecord value) throws SQLException {
	return valuesService.saveValue(itemID, value);
    }

    @Override
    public int findSupplierID(String supplierName) throws SQLException {
	return finderService.findSupplierID(supplierName);
    }

    /**
     * Saves new class to classes table
     * 
     * @param name
     *            name of new class
     * @return id of new class in the table
     */
    @Override
    public int saveClass(final String name) throws SQLException {
	return saverService.saveClass(name);
    }

    /**
     * Saves new manufacturer to manufacturers table in database
     * 
     * @param name
     *            manufacturer`s name
     * @param webSite
     *            manufacturer`s webSite
     * @return id of new manufacturer in the table
     * @throws SQLException
     */
    @Override
    public int saveManufacturer(final String name, final String webSite) throws SQLException {
	return saverService.saveManufacturer(name, webSite);
    }

    /**
     * Saves new manufacturer to manufacturers table in database
     * 
     * @param manufacturer
     *            {@link com.google.code.ptrends.common.entities.Manufacturer}
     *            object to save
     * @return id of new manufacturer in the table
     * @throws SQLException
     */
    private int saveManufacturer(final Manufacturer manufacturer) throws SQLException {
	return saveManufacturer(manufacturer.getName(), manufacturer.getWebSite());
    }

    @Override
    public void saveSession(Session session) throws SQLException {
	int id;
	String querySession = "INSERT INTO sessions (EVENT_TYPE_ID, SUPPLIER_ID, START_DATE, END_DATE, ITEMS_COUNT) values(?,?,?,?,?)";
	PreparedStatement preparedStatement = null;
	ResultSet set = null;
	try {
	    preparedStatement = connection.prepareStatement(querySession);
	    preparedStatement.setInt(1, session.getState().ordinal() + 1);
	    preparedStatement.setInt(2, SupplierIdentifiers.RET);
	    preparedStatement.setString(3, dateformat.format(session.getStartDate()));
	    preparedStatement.setString(4, dateformat.format(session.getEndDate()));
	    preparedStatement.setInt(5, session.getItemsCount());
	    preparedStatement.executeUpdate();
	} catch (SQLException e) {
	    LOG.error("Problem with save session", e);
	    throw e;
	} finally {
	    closeStatement(preparedStatement);
	}
	try {
	    querySession = "SELECT MAX(id) FROM sessions";
	    preparedStatement = connection.prepareStatement(querySession);
	    set = preparedStatement.executeQuery();
	    if (set.next()) {
		id = set.getInt(1);
		saveSessionEvents(id, session);
	    }
	} catch (SQLException e) {
	    LOG.error("Problem with get session id");
	} finally {
	    close(set, preparedStatement);
	}
    }

    private void saveSessionEvents(int id, Session session) throws SQLException {
	PreparedStatement preparedStatement = null;
	String querySessionEvents = "INSERT INTO session_events (EVENT_TYPE_ID, SESSIONS_ID, NAME, DESCRIPTION, EVENT_DATE) values(?,?,?,?,?)";
	try {
	    preparedStatement = connection.prepareStatement(querySessionEvents);
	    for (SessionEvent event : session.getEvents()) {
		if (event == null)
		    continue;
		preparedStatement.setInt(1, event.getStatus().ordinal() + 1);
		preparedStatement.setInt(2, id);
		preparedStatement.setString(3, event.getName());
		preparedStatement.setString(4, event.getDescription());
		preparedStatement.setString(5, dateformat.format(event.getEventDate()));
		preparedStatement.executeUpdate();
	    }
	} catch (SQLException e) {
	    LOG.error("Problem with save session", e);
	    throw e;
	} finally {
	    closeStatement(preparedStatement);
	}
    }

    /**
     * main persisting procedure
     * 
     * @param items
     *            Collection of {@link com.google.code.ptrends.entities.ETLItem}
     *            objects
     * @author Daniel
     */
    @Override
    public void persistData(final Collection<ETLItem> items) {
	for (ETLItem item : items) {
	    try {
		String categoryName = item.getCategory().name();
		int categoryID = finderService.findCategoryID(categoryName);
		if (categoryID == NOT_FOUND_ID) {
		    try {
			categoryID = saverService.saveCategory(categoryName);
		    } catch (Exception e) {
			LOG.error("Error persisting item: Category " + item.getCategory().name()
				+ " not found");
			continue;
		    }
		}
		ItemRecord itemRecord = findItem(item.getItemModel(), categoryID);
		if (itemRecord == null) {
		    final int itemID;

		    String itemClassName = item.getItemClass().getName();
		    int classID = finderService.findClassID(itemClassName);
		    if (classID == NOT_FOUND_ID) {
			classID = saveClass(item.getItemClass().getName());
		    }

		    String manufacturerName = item.getManufacturer().getName();
		    int manufacturerID = finderService.findManufacturerID(manufacturerName);
		    if (manufacturerID == NOT_FOUND_ID) {
			manufacturerID = saveManufacturer(item.getManufacturer());
		    }

		    itemRecord = new ItemRecord();
		    itemRecord.setCategoryID(categoryID);
		    itemRecord.setClassID(classID);
		    itemRecord.setManufacturerID(manufacturerID);
		    itemRecord.setModel(item.getItemModel());
		    itemID = this.saveItem(itemRecord);

		    PriceRecord pr = new PriceRecord();
		    pr.setItemID(itemID);
		    pr.setPrice(item.getPrice());
		    pr.setSupplierID(item.getSupplierID());
		    pr.setPriceDate(item.getPriceDate());
		    this.saveNewPrice(pr);

		    if (item.getParameters() != null) {
			if (item.getParameters().size() != 0) {
			    ItemValueCollection ivc = makeItemValueCollection(null, item, itemID);
			    valuesService.saveValueCollection(ivc);
			}
		    }
		} else {
		    final PriceRecord concretePriceRecord;
		    final int itemID = itemRecord.getItemID();
		    final float itemPrice = item.getPrice();
		    final Date itemPriceDate = item.getPriceDate();

		    int supplierID = item.getSupplierID();

		    concretePriceRecord = priceService.findPrice(itemID, supplierID, itemPriceDate);
		    if (concretePriceRecord == null) {
			saveNewPrice(itemID, supplierID, itemPrice, itemPriceDate);
		    } else {
			// if (concretePriceRecord.getPrice()!= itemPrice)
			updatePrice(concretePriceRecord, itemPrice);
		    }

		    ItemValueCollection existingValues = valuesService.getValues(itemID);
		    ItemValueCollection newValues = makeItemValueCollection(existingValues, item,
			    itemID);
		    if (!newValues.isEmpty())
			valuesService.saveValueCollection(newValues);
		}

	    } catch (SQLException e) {
		LOG.error("Error persisting item", e);
		continue;
	    } catch (IllegalArgumentException e) {
		LOG.error("Error persisting item", e);
		continue;
	    }
	}
    }

    private ItemValueCollection makeItemValueCollection(ItemValueCollection existingIVR,
	    ETLItem item, int itemID) throws SQLException {
	ItemValueCollection ivc = new ItemValueCollection();
	ivc.setItemID(itemID);
	ItemValueRecord ivr = null;
	int parameterID;
	if (existingIVR == null) {
	    for (Property property : item.getParameters()) {
		parameterID = finderService.findParameterID(property.getName());
		ivr = new ItemValueRecord();
		ivr.setParametrID(parameterID);
		ivr.setValue(property.getValue());
		ivc.add(ivr);
	    }
	} else {
	    for (Property prop : item.getParameters()) {
		parameterID = finderService.findParameterID(prop.getName());
		if (!existingIVR.haveParameter(parameterID)) {
		    ivr = new ItemValueRecord();
		    ivr.setParametrID(parameterID);
		    ivr.setValue(prop.getValue());
		    ivc.add(ivr);
		}
	    }
	}

	return ivc;
    }

}